package serverapp;

import java.sql.*;

public class Database
{
	static String dataSourceName;
	static String dbURL;
	static Connection con;
	static Statement s;
	
	public static void initialize()
	{
		try 
    	{
	        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	        dataSourceName = "DVDStore.mdb";
	        dbURL = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" + dataSourceName;
             //dbURL = "jdbc:odbc:" + DVDStore;

	        con = DriverManager.getConnection(dbURL, "","");
	        s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
	    }
	    catch (Exception e)
	    {
	    	System.out.println("Error caught in Database Initialize paragraph");
	    }
	}
	
	public static void createTables()
	{
		try 
    	{	        
	        PreparedStatement createCustomer = con.prepareStatement("create table Customer(PhoneNum text(40),Surname text(40),FirstName text(40),Credit number, constraint Customerpk primary key (PhoneNum))");			
   			createCustomer.executeUpdate();
   			System.out.println("Customer table created successfully");
    	}
		catch (Exception err) 
		{
        	System.out.println("Error while creating Customer table\nERROR: " + err);
    	}
    	try 
    	{	        
	        PreparedStatement createDVD = con.prepareStatement("create table DVD(DVDCode counter, PhoneNum Text(40), Title Text(40), Category Text(40), NewRelease YesNo, Available YesNo, constraint DVDpk primary key (DVDCode), constraint DVDfk foreign key (PhoneNum) references Customer(PhoneNum));");			
   			createDVD.executeUpdate();
   			System.out.println("DVD table created successfully");
    	}
		catch (Exception err)
		{
        	System.out.println("Error while creating DVD table\nERROR: " + err);
    	}
	}
	
	public static void insertCustomer(String p, String s, String f, double c)
	{
		try 
    	{	        
	        PreparedStatement insert = con.prepareStatement("INSERT INTO Customer VALUES (?,?,?,?)");
	        insert.setString(1,p);
	        insert.setString(2,s);
	        insert.setString(3,f);
	        insert.setString(4, Double.toString(c));
   			insert.executeUpdate();
   			System.out.println("Customer record added successfully");
    	}
		catch (Exception err)
		{
        	System.out.println("Error while inserting into Customer table\nError: " + err);
    	}
	}
	
	public static void insertDVD(String t, String c, boolean n, boolean a)
	{
		try 
    	{	        
	        PreparedStatement insert = con.prepareStatement("INSERT INTO DVD(Title, Category, NewRelease, Available) VALUES (?,?,?,?)");
	        insert.setString(1,t);
	        insert.setString(2,c);
	        insert.setBoolean(3,n);
	        insert.setBoolean(4,a);
   			insert.executeUpdate();
   			System.out.println("DVD record added successfully");
    	}
		catch (Exception err) 
		{
        	System.out.println("Error while inserting DVD table\nError: " + err);
    	}
	}
	
	public static ResultSet selectQuery(String query)
    {
    	ResultSet rs = null;
    	try
    	{
    		s.executeQuery(query); // select the data from the table
	    	rs = s.getResultSet(); // get any ResultSet that came from our query
	    	//System.out.println("\n"+rs.getString(3)+"\n");
    	}
    	catch (Exception e)
    	{
    		System.out.println("Error while in Database.selectQuery");
    	}
    	return rs;
    }

	public static int findDVDsOutByCustomerCount(String phoneNum) // Stuart
	{
		int Size = 0;
    	ResultSet rs = null;
    	try 
    	{	        
	        PreparedStatement findDVDsOutByCustomer = con.prepareStatement("Select count(*) from DVD where PhoneNum = ?");
	        findDVDsOutByCustomer.setString(1,phoneNum);
   			findDVDsOutByCustomer.executeQuery();
   			rs = findDVDsOutByCustomer.getResultSet();
   			rs.next();
	    	Size = Integer.parseInt(rs.getString(1));
	    	System.out.println("DVDs Out by Customer Count successfully = " + Size);
    	}
		catch (Exception err) 
		{
        	System.out.println("Error while DVDs Out by Customer successfully\nError: " + err);
    	}
    	return Size;
	}
	
	public static ResultSet findDVDsOutByCustomerQuery(String oldNum) // Stuart
	{
		ResultSet rs = null;
    	try 
    	{	        
	        PreparedStatement findDVDsOutByCustomer = con.prepareStatement("Select DVDCode FROM DVD where PhoneNum = ?");
	        findDVDsOutByCustomer.setString(1,oldNum);
   			findDVDsOutByCustomer.executeQuery();
   			rs = findDVDsOutByCustomer.getResultSet();
   			System.out.println("findDVDsOutByCustomerQuery found successfully");
    	}
		catch (Exception err) 
		{
        	System.out.println("Error while DVDs Out by Customer\nError: " + err);
    	}
    	return rs;
	}

	public static void updateDVD(String DVDCode)
	{
		try
    	{    		
    		PreparedStatement update = con.prepareStatement("UPDATE DVD SET Available = false, PhoneNum = NULL WHERE DVDCode = ?");//create statement on connection			
			update.setInt(1,Integer.parseInt(DVDCode));
			update.executeUpdate();
			System.out.println("DVD return successfully");
		}
		catch (Exception err) 
		{
        	System.out.println("Error while returning DVD - stu\nERROR: " + err);
    	}
	}
	
	public static void updateCustomerNotNumber(Customer cust)
	{
		try
    	{    		
    		PreparedStatement update = con.prepareStatement("UPDATE Customer SET FirstName = ?, Surname = ? WHERE PhoneNum = ?");//create statement on connection			
			
			update.setString(1,cust.getName());
	        update.setString(2,cust.getSurname());
	        update.setString(3,cust.getPhoneNum());
			update.executeUpdate();
			System.out.println("Customer record updated successfully");
		}
		catch (Exception err) 
		{
        	System.out.println("Error while updating Customer\nERROR: " + err);
    	}	
	}

	public static void updateCustomer(String oldNum, Customer cust) // Stuart
    {
    	try
    	{    		
    		PreparedStatement update = con.prepareStatement("UPDATE Customer SET PhoneNum = ?, FirstName = ?, Surname = ? WHERE PhoneNum = ?");//create statement on connection			
			update.setString(1,cust.getPhoneNum());
			update.setString(2,cust.getName());
	        update.setString(3,cust.getSurname());
	        update.setString(4,oldNum);
			update.executeUpdate();
			System.out.println("Customer record updated successfully");
		}
		catch (Exception err) 
		{
        	System.out.println("Error while updating Customer\nERROR: " + err);
    	}
    }
    
    public static ResultSet findCustomerQuery(String p)
    {
    	ResultSet rs = null;
    	try 
    	{	        
	        PreparedStatement findCustomer = con.prepareStatement("Select FirstName, Surname, Credit from Customer where PhoneNum = ?");
	        findCustomer.setString(1,p);
   			findCustomer.executeQuery();
   			rs = findCustomer.getResultSet();
   			System.out.println("Customer found successfully");
    	}
		catch (Exception err) 
		{
        	System.out.println("Error while finding Customer\nError: " + err);
    	}
    	return rs;
    }
    
    public static int findCustomerCount(String p)
    {
    	int Size = 0;
    	ResultSet rs = null;
    	try 
    	{	        
	        PreparedStatement findCustomer = con.prepareStatement("Select count(*) from Customer where PhoneNum = ?");
	        findCustomer.setString(1,p);
   			findCustomer.executeQuery();
   			rs = findCustomer.getResultSet();
   			System.out.println("Customer-found count successfully");
   			rs.next();
	    	Size = Integer.parseInt(rs.getString(1));
    	}
		catch (Exception err) 
		{
        	System.out.println("Error while finding Customer count\nError: " + err);
    	}
    	return Size;
    }
    
    public static void addCreditToCustomer(String p, Double c)
    {
    	try
    	{    		
    		PreparedStatement update = con.prepareStatement("UPDATE Customer SET Credit = (Credit + ?) WHERE PhoneNum = ?");//create statement on connection			
			update.setDouble(1,c);
	        update.setString(2,p);
			update.executeUpdate();
			System.out.println("Customer record updated successfully");
		}
		catch (Exception err) 
		{
        	System.out.println("Error while updating\nERROR: " + err);
    	}
    }
    
    public static void updateCustomer(Customer cust)
    {
    	try
    	{    		
    		PreparedStatement update = con.prepareStatement("UPDATE Customer SET FirstName = ?, Surname = ?, Credit = ? WHERE PhoneNum = ?");//create statement on connection			
			update.setString(1,cust.getName());
	        update.setString(2,cust.getSurname());
	        update.setDouble(3,cust.getCredit());
	        update.setString(4,cust.getPhoneNum());
			update.executeUpdate();
			System.out.println("Customer record updated successfully");
		}
		catch (Exception err) 
		{
        	System.out.println("Error while updating\nERROR: " + err);
    	}
    }
    
    public static void updateQuery(String query)
    {
    	try 
    	{	        
	        s.executeUpdate(query);
   			//System.out.println("Update in D");
    	}
		catch (Exception err) 
		{
        	System.out.println("Error while trying to update table: " + err);
    	}	
    }
    
    public static int getSize(String select)
    {
    	int Size = 0;
    	ResultSet rs = null;
    	try
    	{
    		s.executeQuery(select);
	    	rs = s.getResultSet();
	    	rs.next();
	    	Size = Integer.parseInt(rs.getString(1));
	    	System.out.println("Size = " + Size);	    		    	
    	}
    	catch (Exception e)
    	{
    		System.out.println("Error while in getSize");
    	}
    	return Size;
    }
    
    public static ResultSet queryAllDVDs()
    {
    	ResultSet rs = null;
    	try
    	{
    		s.executeQuery("select * from DVD;");
	    	rs = s.getResultSet();
    	}
    	catch (Exception e)
    	{
    		System.out.println("Error while in queryAllDVDs");
    	}
    	return rs;
    }
    
    public static void deleteCustomer(String strPhoneNum)
	{
		try 
    	{	        
	        PreparedStatement delete = con.prepareStatement("DELETE * FROM Customer WHERE PhoneNum=?;");
	        delete.setString(1, strPhoneNum);
   			delete.executeUpdate();
   			System.out.println("Customer record has been successfully deleted.");
    	}
		catch (Exception err) 
		{
        	System.out.println("Error while inserting DVD table\nError: " + err);
    	}
	}
    
    public static void deleteAllMoviesTakenOutByCustomer(String strPhoneNum)
	{
		try 
    	{	        
	        PreparedStatement delete = con.prepareStatement("DELETE * FROM DVD WHERE PhoneNum=?;");
	        delete.setString(1, strPhoneNum);
   			delete.executeUpdate();
   			System.out.println("DVD record(s) has been successfully deleted.");
    	}
		catch (Exception err) 
		{
        	System.out.println("Error while inserting DVD table\nError: " + err);
    	}
	}
    
    public static void deleteDVD(String strDVDCode)
	{
		try 
    	{	        
	        PreparedStatement delete = con.prepareStatement("DELETE * FROM DVD WHERE DVDCode=?;");
	        delete.setString(1, strDVDCode);
   			delete.executeUpdate();
   			System.out.println("DVD record has been successfully deleted.");
    	}
		catch (Exception err) 
		{
        	System.out.println("Error while inserting DVD table\nError: " + err);
    	}
	}
    
    public static int getMoviesStartWithSize(String startWith)
    {
    	int Size = 0;
    	ResultSet rs = null;
    	try
    	{
    		String movieQuery = "SELECT count(*) FROM DVD WHERE LEFT(Title, ?) = ?;";
    		PreparedStatement select = con.prepareStatement(movieQuery);
	        select.setString(1, Integer.toString(startWith.length()));
	        select.setString(2, startWith);
	        
   			select.executeQuery();
   			//System.out.println("NB: statement executed!");
	    	rs = select.getResultSet();
	    	rs.next();  		    	
	    	Size = Integer.parseInt(rs.getString(1));
    	}
    	catch (Exception e)
    	{
    		System.out.println("Error while in getMoviesStartWithSize");
    	}
    	return Size;
    }
    
    public static ResultSet selectMoviesStartWithQuery(String startWith)
    {
    	ResultSet rs = null;
    	try
    	{
    		//PreparedStatement select = con.prepareStatement("Select DVDCode, Title, Category, NewRelease, Available FROM DVD WHERE LEFT(Title, 1) = 'B';");
    		PreparedStatement select = con.prepareStatement("Select DVDCode, Title, Category, NewRelease, Available FROM DVD WHERE LEFT(Title, ?) = ? ORDER BY Title;");
	        select.setString(1, Integer.toString(startWith.length()));
	        select.setString(2, startWith);
   			select.executeQuery();
	    	rs = select.getResultSet(); // get any ResultSet that came from our query
	    	//System.out.println("\n"+rs.getString(3)+"\n");
    	}
    	catch (Exception e)
    	{
    		System.out.println("Error while in selectgetMoviesStartWithQuery");
    	}
    	return rs;
    }
	
	public static void close()
	{
		try
		{
			s.close(); // close the Statement to let the database know we're done with it
	    	con.close(); // close the Connection to let the database know we're done with it
		}
		catch (Exception e)
		{
			System.out.println("Error in cloing database");	
		}     
	}
}
